package meterDB;

use DBI;
use meterDB_spec;
use strict;

#use AutoLoader 'AUTOLOAD';


#__END__

sub new
{
    my ($self) = @_;

    my $config = {'db_user' => 'brain',
                  'db_pw' => 'brainpasswd',
                  'dsn' => 'DBI:SQLite:dbname=meters.db'
                  };

    my $class = ref($self) || $self;
    my $realdbh = DBI->connect($config->{dsn},$config->{db_user},$config->{db_pw});

    my $dbspec = meterDB_spec->new();

    my $object = { 'dbh' => $realdbh,
                   'dbmeta' => $dbspec,
                   };

    bless $object, $class;
    return $object;
}

sub close_db
{
    my ($self) = @_;
    $self->{dbh}->disconnect() if (defined($self->{dbh}));
    $self->{dbh} = undef;
}

sub do
{
    my ($self,$text) = @_;
    return $self->{dbh}->do($text);
}

sub new_record
{
    my ($self, $table, $recdata) = @_;

    my $tablespec = $self->{dbmeta}->table($table);

    my $record = {};

    foreach my $field (@{$tablespec->{fields}})
    {
        my $fieldname = $field->{field};
        if (exists($recdata->{$fieldname}))
        {
            $record->{$fieldname} = $recdata->{$fieldname};
        }
        else
        {
            $record->{$field->{field}} = ($field->{type} =~ m/(int|real)/ ? 0 : '');
        }
    }

    return $record;
}

sub build_search_criteria
{
    my ($self,$table,$criteria) = @_;
#        print "meterDB.pm build_search_criteria - $criteria!!!";

    $self->{criteria}->{criteria} = [];
    $self->{criteria}->{tables} = [];

    if (ref($criteria) eq 'HASH')
    {
        foreach my $crit (sort keys %{$criteria})
        {
            print STDERR "Building criteria based on $crit\n";
            push @{$self->{criteria}->{criteria}},"$crit = " . $self->{dbh}->quote($criteria->{$crit});
        }
        if (ref($table) eq 'SCALAR')
        {
            push @{$self->{criteria}->{tables}},$table;
        }
        elsif (ref($table) eq 'ARRAY')
        {
            foreach my $tab (@{$table})
            {
                push @{$self->{criteria}->{tables}},$tab;
            }
        }
    }
    elsif (ref($criteria) eq 'ARRAY')
    {
        push @{$self->{criteria}->{criteria}},$self->build_search_criteria_smart($criteria);
    }

    if (scalar @{$self->{criteria}->{criteria}} > 0)
    {
        return ' where ' . join(' and ',@{$self->{criteria}->{criteria}});
    }
    else
    {
        return '';
    }
}

sub buildfieldlist
{
    my ($key,$singlefield) = @_;
    
    my $fieldlist = '*';

    if (defined($singlefield) && ref($singlefield) eq 'ARRAY')
    {
        my $fields = {$key => 1,};
        foreach my $field(@{$singlefield})
        {
            $fields->{$field} = 1;
        }
        $fieldlist = join(',',keys %{$fields});
    }
    elsif (defined($key) && defined($singlefield))
    {
        $fieldlist = "$key,$singlefield";
    }
    elsif (defined($singlefield))
    {
        $fieldlist = "$singlefield";
    }
    return $fieldlist;
}

sub build_search_criteria_smart
{
    my ($self, $smart) = @_;

    my @criteria = ();

    if (ref($smart) eq 'ARRAY')
    {
        foreach my $frag (@{$smart})
        {
            push @criteria,$self->build_search_criteria_smart($frag);
        }
        return sprintf('( %s )',join(' ',@criteria));
    }
    elsif (ref($smart) eq 'HASH')
    {
        return sprintf('%s.%s %s %s',
                       $smart->{t},
                       $smart->{f},
                       $smart->{m},
                       $self->{dbh}->quote($smart->{v}));
    }
    elsif ((ref($smart) eq 'SCALAR') ||
           (ref($smart) eq ''))
    {
        return $smart;
    }
}

sub del_generic
{
    my ($self,$table,$criteria) = @_;

    my $query = "delete from $table " . $self->build_search_criteria($table,$criteria);

    $self->{dbh}->do($query);
}

sub add_generic
{
    my ($self, $table, $record) = @_;

    my (@fields,@values) = ((),());
#
# GJ  added code to replace id=0 with id=null to make auto increment work in sqlite.
#
    foreach my $field (sort keys %{$record})
    {
        next if ($field =~ m/^_/);
	if (( $field eq 'id' ) && ( $record->{$field} == 0 )) {
	    print STDERR "field=",$field," record=",$self->{dbh}->quote($record->{$field}),"\n";
	    push @fields,$field;
	    push @values,'NULL';
	} else {
	    push @fields,$field;
	    push @values,$self->{dbh}->quote($record->{$field});
	}
    }

    my $query = "insert into $table (" . 
        join(', ',@fields) . 
            ") values (" . 
                join(', ',@values) . 
                    ")";
    print STDERR "add_generic: query=",$query,"\n";


    $self->{dbh}->do($query);

    return ($self->{dbh}->func('last_insert_rowid'));
}

sub update_generic_record
{
    my ($self, $table, $criteria, $record) = @_;

    my $oldrecord = $self->get_generic_single($table,$criteria);

    foreach my $field (keys %{$record})
    {
        $oldrecord->{$field} = $record->{$field};
    }

    my @pairs = ();

    foreach my $field (keys %{$oldrecord})
    {
        push @pairs, sprintf('%s = %s',$field,$self->{dbh}->quote($oldrecord->{$field}));
    }

    return (1) if (scalar @pairs == 0);

    my $query = "update $table set " . 
        join(', ',@pairs) . $self->build_search_criteria($table,$criteria);

    $self->{dbh}->do($query);
    return $oldrecord;
}

sub get_generic_single
{
    my ($self,$table,$criteria) = @_;

    my $fields = $self->{dbmeta}->getfieldlist($table);

    my $query = 'select ' . join(', ',@{$fields}) . " from $table " . $self->build_search_criteria($table,$criteria);

    print STDERR "get_generic_single: query=",$query,"\n";

    my (@fielddata) = $self->{dbh}->selectrow_array($query);

    return undef unless(scalar @fielddata > 0);

    my $record = {};

    for(my $i=0;$i<scalar @fielddata;$i++)
    {
        $record->{$fields->[$i]} = $fielddata[$i];
    }
    return $record;
}

sub get_generic_multi
{
    my ($self,$table,$key,$criteria,$singlefield) = @_;

 #   print "get_generic_multi - criteria=$criteria</br>";

    my $query = sprintf('select %s from %s %s',
                        buildfieldlist($key,$singlefield),
                        $table,
                        $self->build_search_criteria($table,$criteria)
                        );

    my $records = (defined($key) ? {} : []);

    my $sth = $self->{dbh}->prepare($query);
    $sth->execute();
    while(my $row = $sth->fetchrow_hashref())
    {
        if (defined($key))
        {
            if (defined($singlefield) && ref($singlefield) eq '')
            {
                $records->{$row->{$key}} = $row->{$singlefield}
            }
            else
            {
                $records->{$row->{$key}} = $row;
            }
        }
        else
        {
            if (defined($singlefield) && ref($singlefield) eq '')
            {
                push @{$records},$row->{$singlefield}
            }
            else
            {
                push @{$records},$row;
            }
        }
    }
    $sth->finish();

    return $records;
}


sub get_generic_sql
{
    my ($self,$table,$key,$sql,$singlefield) = @_;
#    print "\nget_generic_sql\n";

    my $query = $sql;

    my $records = (defined($key) ? {} : []);

#    print "sql=",$sql,"\n";
    my $sth = $self->{dbh}->prepare($query);
    $sth->execute();
    while(my $row = $sth->fetchrow_hashref())
    {
        if (defined($key))
        {
            if (defined($singlefield) && ref($singlefield) eq '')
            {
                $records->{$row->{$key}} = $row->{$singlefield}
            }
            else
            {
                $records->{$row->{$key}} = $row;
            }
        }
        else
        {
            if (defined($singlefield) && ref($singlefield) eq '')
            {
                push @{$records},$row->{$singlefield}
            }
            else
            {
                push @{$records},$row;
            }
        }
    }
    $sth->finish();

    return $records;
}


1;
